package cn.edu.tsinghua.smxq.mobilecity.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.tsinghua.smxq.mobilecity.po.ElectricityRecord;
import cn.edu.tsinghua.smxq.mobilecity.po.User;
import cn.edu.tsinghua.smxq.mobilecity.utils.DBUtils;

public class ElectricityRecordDAO {

	public boolean create(ElectricityRecord electricityRecord) throws SQLException {
		boolean success = false;
		Connection conn = DBUtils.Connect();
		PreparedStatement pstmt = conn
				.prepareStatement("INSERT INTO mobilecity.electricityrecord(userid,cityid,month,usageelectricity,unitprice,rate,haspaid) values(?,?,?,?,?,?,?)");
		pstmt.setInt(1, electricityRecord.getUserId());
		pstmt.setInt(2, electricityRecord.getCityId());
		pstmt.setInt(3, electricityRecord.getMonth());
		pstmt.setFloat(4, electricityRecord.getUsage());
		pstmt.setFloat(5, electricityRecord.getUnitPrice());
		pstmt.setFloat(6, electricityRecord.getRate());
		pstmt.setInt(7, electricityRecord.getHasPaid());
	
		if (pstmt.executeUpdate() == 1) {
			success = true;
		}
		pstmt.close();
		conn.close();
		return success;
	}
	
	public List<ElectricityRecord> getAllByUserId(int userId) throws SQLException {
		List<ElectricityRecord> electricityRecordList = new ArrayList<ElectricityRecord>();
		Connection conn = DBUtils.Connect();
		ResultSet rs = null;
		PreparedStatement pstmt = conn
				.prepareStatement("SELECT * FROM mobilecity.electricityrecord WHERE userid=?");
		pstmt.setInt(1, userId);
		rs = pstmt.executeQuery();
		ElectricityRecord electricityRecord;
		while (rs.next()) {
			electricityRecord = new ElectricityRecord();
			electricityRecord.setId(rs.getInt("id"));
			electricityRecord.setUserId(rs.getInt("userid"));
			electricityRecord.setCityId(rs.getInt("cityid"));
			electricityRecord.setMonth(rs.getInt("month"));
			electricityRecord.setUsage(rs.getFloat("usageelectricity"));
			electricityRecord.setRate(rs.getFloat("rate"));
			electricityRecord.setUnitPrice(rs.getFloat("unitprice"));
			electricityRecord.setHasPaid(rs.getInt("haspaid"));
			electricityRecordList.add(electricityRecord);
		}
		rs.close();
		pstmt.close();
		conn.close();
		return electricityRecordList;
	}
	
	public ElectricityRecord getOneByUserId(int userId,int month) throws SQLException {
		ElectricityRecord electricityRecord = null;
		Connection conn = DBUtils.Connect();
		ResultSet rs = null;
		PreparedStatement pstmt = conn
				.prepareStatement("SELECT * FROM mobilecity.electricityrecord WHERE userid=? and month=?");
		pstmt.setInt(1, userId);
		pstmt.setInt(2, month);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			electricityRecord = new ElectricityRecord();
			electricityRecord.setId(rs.getInt("id"));
			electricityRecord.setUserId(rs.getInt("userid"));
			electricityRecord.setCityId(rs.getInt("cityid"));
			electricityRecord.setMonth(rs.getInt("month"));
			electricityRecord.setUsage(rs.getFloat("usageelectricity"));
			electricityRecord.setRate(rs.getFloat("rate"));
			electricityRecord.setUnitPrice(rs.getFloat("unitprice"));
			electricityRecord.setHasPaid(rs.getInt("haspaid"));
		}
		rs.close();
		pstmt.close();
		conn.close();
		return electricityRecord;
	}
	
	public boolean updateHasPaid(int id) throws SQLException {
		boolean success = false;
		Connection conn = DBUtils.Connect();
		PreparedStatement pstmt = conn
				.prepareStatement("UPDATE mobilecity.electricityrecord SET haspaid=? WHERE id=?");
		pstmt.setInt(1, 1);
		pstmt.setInt(2, id);
	
		if (pstmt.executeUpdate() == 1) {
			success = true;
		}
		pstmt.close();
		conn.close();
		return success;
	}
	
	
	
}
